/********************************************************************************
Script name	: StarterKit.CreateDb
Description	: Create database
********************************************************************************/

USE master
GO

DECLARE 
	@physical_device_name nvarchar(260),
	@backup_set_name sysname,	
	@database_name sysname, 
	@stmt nvarchar(4000), 
	@defaultFilesPath nvarchar(4000)

SET @database_name = '$(StarterKitDb)'

SET @physical_device_name = @database_name + '_' +
	CONVERT(varchar(50),GETDATE(),112)+REPLACE(CONVERT(varchar(50),GETDATE(), 114),':','') + '.bak'
SET @backup_set_name = @database_name + N' backup'

IF EXISTS (SELECT * FROM master..sysdatabases WHERE [name] = @database_name )
BEGIN
	--do rollback all active transactions
	SET @stmt = 'ALTER DATABASE '+@database_name+' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
	EXEC sp_executesql @stmt

	SET @stmt = 'ALTER DATABASE '+@database_name+' SET MULTI_USER'
	EXEC sp_executesql @stmt

	BACKUP DATABASE @database_name TO  DISK = @physical_device_name 
	WITH  NOINIT ,  NOUNLOAD ,  
	NAME = @backup_set_name,  NOSKIP ,  STATS = 10, NOFORMAT 
	
	
	IF '$(NewDB)' = 1
	BEGIN
		SET @stmt = 'PRINT ''DROP DATABASE '+@database_name+''''
		EXEC sp_executesql @stmt

		SET @stmt = 'DROP DATABASE '+@database_name
		EXEC sp_executesql @stmt
	END
	ELSE BEGIN
		IF OBJECT_ID('tempdb..#Convertation') IS NOT NULL 
			DROP TABLE #Convertation

		CREATE TABLE #Convertation(Version nvarchar(10))

		INSERT INTO #Convertation(Version) VALUES('$Version')
	END
END

IF NOT EXISTS (SELECT * FROM master..sysdatabases WHERE [name] = @database_name)
BEGIN
	SELECT TOP 1 @defaultFilesPath = [filename]
	FROM model.dbo.sysfiles

	SET @defaultFilesPath = REVERSE(SUBSTRING(REVERSE(@defaultFilesPath),CHARINDEX('\',
			REVERSE(@defaultFilesPath)),LEN(@defaultFilesPath)))

	SET @stmt = '
	CREATE DATABASE '+@database_name+'
	ON  PRIMARY 
		(NAME = StarterKitDATA, 
		FILENAME = '''+@defaultFilesPath+@database_name+'_data.mdf'',
		SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB )
	LOG ON 
		(NAME = StarterKitLOG, 
		FILENAME = '''+@defaultFilesPath+@database_name+'_log.ldf'',
		SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
	COLLATE Latin1_General_CI_AS'

	EXEC sp_executesql @stmt
	SET @stmt = 'ALTER DATABASE '+@database_name+' SET QUOTED_IDENTIFIER ON, ANSI_WARNINGS OFF, ANSI_PADDING OFF, ANSI_NULLS OFF'
	EXEC sp_executesql @stmt
END
GO